# -*- conding:UTF-8 -*-

'''
@ Project: code
@ File: student.py
@ Author: 张志坤
@ Date：2022/5/18 18:39

'''
import toml
import json
# 导入flask
from flask import Flask
# 导入pymysql
import pymysql
# 导入settings里的DefaultConfig
from settings import DefaultConfig

# 实例化 flask对象
app = Flask(__name__)

# 从对象中读取配置
app.config.from_object(DefaultConfig)

# app.config.from_pyfile("settings")

# 获取setting中的数据库信息
port = app.config.get("MYSQL_PORT")
host = app.config.get("MYSQL_HOST")

# 连接数据库
coon = pymysql.connect(user='root', password='100352@lp', port=port, host=host,
                       charset="utf8",
                       database="student518")
# 创建游标对象
cou = coon.cursor()
print(coon, "////", cou)


@app.route("/create")
def create():
    """创建表"""
    try:
        # sql语句
        sql = """create table student(
            id int primary key auto_increment,
            name varchar(20),
            age int,
            gender enum("男","女")
            )
            """
        # 游标对象执行sql语句
        cou.execute(sql)
    except Exception as e:
        print("创建失败,表已存在")
        return "创建失败,表已存在"
    print("创建成功")
    return "创建成功"


@app.route("/insert")
def insert():
    sql = """insert into student values(0,"婷",22,2),(0,"孙博",21,1),(0,"秋秋",23,1),(0,"瑾瑾",19,2)
        """
    data = cou.execute(sql)
    print(data)
    if data:
        print("插入数据成功")
        coon.commit()
        return "插入数据成功"
    else:
        print("插入数据失败")
        coon.rollback()
        return "插入数据失败"


@app.route("/delete")
def delete():
    sql = """delete from student where gender="女"
        """
    data = cou.execute(sql)
    if data:
        print("删除数据成功")
        coon.commit()
        return "删除数据成功"
    else:
        print("删除数据失败")
        coon.rollback()


@app.route("/update")
def update():
    sql = """update student set name = "我被修改了" where gender="男"
        """
    data = cou.execute(sql)
    # data = json.dumps(data)
    if data:
        print("更新数据成功", cou.fetchall())
        coon.commit()
    else:
        print("更新数据失败")
        coon.rollback()
    return "更新数据成功"


@app.route("/select")
def select():
    sql = """select * from student
        """
    data = cou.execute(sql)

    print("查询数据成功", data)
    list = []
    for i in range(data):
        print(cou.fetchone())
        list.append(cou.fetchone())

    return "查询数据成功" + json.dumps(list)


if __name__ == "__main__":
    app.run()
